package com.zxx.sql;

import java.util.ArrayList;

import com.zxx.common.Common;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.View;
import android.view.View.OnClickListener;

/**
 * @brief 直接操作数据库类，sqlman调用该类
 * @author Administrator
 *
 */
public class DataBaseHelper extends SQLiteOpenHelper {

	private static final String DB_NAME					 = "zxxhouseDb.sqlite";
	private static final String TABLE_ZXXHOUSE 			 = "zxxhouse";
	
	private static final int VERSION = 1;//第一版数据库版本号，以后每次改动都要相应增加
	private static final String COLUMN_ZXXHOUSE_ID 			= "id";
	private static final String COLUMN_ZXXHOUSE_TITLE 		= "title";
	private static final String COLUMN_ZXXHOUSE_DEPAPTMENT 	= "departMent";
	private static final String COLUMN_ZXXHOUSE_ADDRESS 	= "address";
	private static final String COLUMN_ZXXHOUSE_TOTALORICE = "totalprice";
	private static final String COLUMN_ZXXHOUSE_PMPRICE 	= "pmPrice";
	private static final String COLUMN_ZXXHOUSE_ORIENTATION = "orientation";
	private static final String COLUMN_ZXXHOUSE_HOUSETYPE   = "houseType";
	private static final String COLUMN_ZXXHOUSE_DECORATE    = "decorate";
	private static final String COLUMN_ZXXHOUSE_AREA	    = "area";
	private static final String COLUMN_ZXXHOUSE_BUILDTYPE   = "buildType";
	private static final String COLUMN_ZXXHOUSE_LOUCHEN     = "louchen";
	private static final String COLUMN_ZXXHOUSE_BUILDAGE     = "buildAge";
	private static final String COLUMN_ZXXHOUSE_TEL		     = "tel";
	private static final String COLUMN_ZXXHOUSE_PICPATH1     = "picPath1";
	private static final String COLUMN_ZXXHOUSE_PICPATH2     = "picPath2";
	private static final String COLUMN_ZXXHOUSE_PICPATH3     = "picPath3";
	private static final String COLUMN_ZXXHOUSE_PICPATH4     = "picPath4";
	private static final String COLUMN_ZXXHOUSE_PICPATH5     = "picPath5";
	
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, VERSION);
		// TODO Auto-generated constructor stub
	}

	 /** 
	  * @brief 建立数据表
	  */
	@Override
	public void onCreate(SQLiteDatabase db) {
		 // create the "zxxhouse" table
		db.execSQL("DROP TABLE IF EXISTS zxxhouse");  
        db.execSQL("CREATE TABLE zxxhouse (id INTEGER PRIMARY KEY, title VARCHAR(50),departMent VARCHAR(50),"+
		 "address VARCHAR(50),totalprice INTEGER NOT NULL DEFAULT 0,pmPrice INTEGER NOT NULL DEFAULT 0,orientation VARCHAR(50),"+
        		"houseType VARCHAR(50),decorate VARCHAR(50),area INTEGER NOT NULL DEFAULT 0,buildType VARCHAR(50),louchen VARCHAR(50),"+
		 "buildAge INTEGER NOT NULL DEFAULT 0,tel VARCHAR(12),picPath1 VARCHAR(50),picPath2 VARCHAR(50),picPath3 VARCHAR(50),picPath4 VARCHAR(50),picPath5 VARCHAR(50))");
	}
	
	 /**
	  * @brief 建立数据表
	  */
	public long insertData(houseData data) {
		//第一种方式:使用android的插入方法
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ZXXHOUSE_TITLE, 		 data.getMtitle());
		cv.put(COLUMN_ZXXHOUSE_DEPAPTMENT, 	 data.getMdepartMentName());
		cv.put(COLUMN_ZXXHOUSE_ADDRESS, 	 data.getMaddress());
		cv.put(COLUMN_ZXXHOUSE_TOTALORICE, 	 data.getMtotalprice());
		cv.put(COLUMN_ZXXHOUSE_PMPRICE, 	 data.getMpmPrice());
		cv.put(COLUMN_ZXXHOUSE_ORIENTATION,  data.getMorientation());
		cv.put(COLUMN_ZXXHOUSE_HOUSETYPE,    data.getMhouseType());
		cv.put(COLUMN_ZXXHOUSE_DECORATE,     data.getMdecorate());
		
		cv.put(COLUMN_ZXXHOUSE_AREA, 	  	 data.getMarea());
		cv.put(COLUMN_ZXXHOUSE_BUILDTYPE, 	 data.getMbuildType());
		cv.put(COLUMN_ZXXHOUSE_LOUCHEN,   	 data.getMlouchen());
		cv.put(COLUMN_ZXXHOUSE_BUILDAGE,     data.getMbuildAge());
		cv.put(COLUMN_ZXXHOUSE_TEL,       	 data.getMtel());
		cv.put(COLUMN_ZXXHOUSE_PICPATH1, 	 data.getMpicPath1());
		cv.put(COLUMN_ZXXHOUSE_PICPATH2,  	 data.getMpicPath2());
		cv.put(COLUMN_ZXXHOUSE_PICPATH3, 	 data.getMpicPath3());
		cv.put(COLUMN_ZXXHOUSE_PICPATH4, 	 data.getMpicPath4());
		cv.put(COLUMN_ZXXHOUSE_PICPATH5,  	 data.getMpicPath5());
		
        return getWritableDatabase().insert(TABLE_ZXXHOUSE, null, cv);
        //第2种方式:直接使用拼接sql语句
        //db.execSQL("INSERT INTO zxxhouse values(null,?,?),new String[] {....}")
        
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	 /**
	  * @brief 按小区选择
	  */
	public ArrayList<houseData> select_byDepart(String departMent,int limitindex,int limitnum) {
		if(null == departMent)
		{
			return sort_byIDL_H(limitindex,limitnum);
		}
		
		String [] agrs = new String[]{departMent,String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where departMent=? order by id asc limit ?,?",agrs);	
	}
	/**
	 * @brief 选取数据库中总价在100Ｗ下的房子
	 * @param limitindex　限制开始
	 * @param limitnum   限制选取数量
	 * @return
	 */
		public ArrayList<houseData> select_byTotalPricebelow100W(int limitindex,int limitnum) {
			String args[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where totalprice <= 100 order by totalprice asc limit ?,?",args);
	}
		/**
		 * @brief 选取数据库中总价在200Ｗ上的房子
		 * @param limitindex　限制开始
		 * @param limitnum   限制选取数量
		 * @return
		 */
	public ArrayList<houseData> select_byTotalPriceabove200W(int limitindex,int limitnum) {
		String args[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where totalprice >= 200 order by totalprice asc limit ?,?",args);
	}
	/**
	 * @brief 选取数据库中总价在100Ｗ——200W之间的房子
	 * @param limitindex　限制开始
	 * @param limitnum   限制选取数量
	 * @return
	 */
	
	public ArrayList<houseData> select_byTotalPricebetween100_200(int limitindex,int limitnum) {
		String args[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where totalprice > 100 and  totalprice < 200 order by totalprice asc limit ?,?",args);
	}
	
	//
	public ArrayList<houseData> select_byHouseType(String room,int limitindex,int limitnum) {
		String sqlTextArr[] = new String[]{room+"%",String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where houseType like ? order by id asc limit ?,?",sqlTextArr);
	}
	
	public ArrayList<houseData> select_byzx(String zxText,int limitindex,int limitnum) {
		String sqlTextArr[] = new String[]{zxText,String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse where decorate = ? order by id asc limit ?,?",sqlTextArr);
	}
	/**
	 * @brief 选取数据库中单价小于1W的房子
	 * @param limitindex　限制开始
	 * @param limitnum   限制选取数量
	 * @return
	 */
	
		public ArrayList<houseData> select_byPmPricebelow1W(int limitindex,int limitnum) {
			String sqlTextArr[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
			return selectSQL("select * from zxxhouse where pmPrice <= 10000 order by pmPrice asc limit ?,?",sqlTextArr);
		}
		/**
		 * @brief 选取数据库中单价在2Ｗ上的房子
		 * @param limitindex　限制开始
		 * @param limitnum   限制选取数量
		 * @return
		 */
		
		public ArrayList<houseData> select_byPmPriceabove2W(int limitindex,int limitnum) {
			String sqlTextArr[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
			return selectSQL("select * from zxxhouse where pmPrice >= 20000 order by pmPrice asc  limit ?,?",sqlTextArr);
		}
		/**
		 * @brief 选取数据库中单价在1Ｗ——2W之间的房子
		 * @param limitindex　限制开始
		 * @param limitnum   限制选取数量
		 * @return
		 */
		
		public ArrayList<houseData> select_byPricebetween1w_2w(int limitindex,int limitnum) {
			String sqlTextArr[] = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
			return selectSQL("select * from zxxhouse where pmPrice > 10000 and  totalprice < 20000 order by pmPrice asc  limit ?,?",sqlTextArr);
		}
		
		
	
	
	public ArrayList<houseData> sort_byPriceH_L(int limitindex,int limitnum) {
        //  "select * from zxxhouse order by start_date asc"
		/*Cursor wrapped = getReadableDatabase().query(TABLE_ZXXHOUSE, 
                null, // all columns 
                COLUMN_RUN_ID + " = ?", // look for a run ID
                new String[]{ String.valueOf(id) }, // with this value
                null, // group by
                null, // order by
                null, // having
                "1"); // limit 1 row
        return new RunCursor(wrapped);*/
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by pmPrice desc limit ?,?",args);
    }
	
	

	public ArrayList<houseData> sort_byPriceL_H(int limitindex,int limitnum)
	{
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by pmPrice asc limit ?,?",args);
	}
	
	
	public ArrayList<houseData> sort_byYearH_L(int limitindex,int limitnum)
	{
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by buildAge desc limit ?,?",args);
	}
			
	
	public ArrayList<houseData> sort_byYearL_H(int limitindex,int limitnum)
	{
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by buildAge asc limit ?,?",args);
	}
		
	public ArrayList<houseData> sort_byIDL_H(int limitindex,int limitnum)
	{
		String [] agrs = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by id asc limit ?,?",agrs);
	}
	
	public ArrayList<houseData> sort_byAreaH_L(int limitindex,int limitnum)
	{
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by area desc limit ?,?",args);
	}
	
	public ArrayList<houseData> sort_byAreaL_H(int limitindex,int limitnum)
	{
		String [] args = new String[]{String.valueOf(limitindex),String.valueOf(limitnum)};
		return selectSQL("select * from zxxhouse order by area asc limit ?,?",args);
	}
	
	/**
	 * @brief 获取数据库按sql语句拼接后查询结果
	 * @param sqlquery 数据库拼接sql语句
	 * @param args 数据库拼接sql语句数组
	 * @return 结果集列表
	 */
	private ArrayList<houseData> selectSQL(String sqlquery,String[] args) {
		
		ArrayList<houseData> list = new ArrayList<houseData>();
		Cursor cursor = getReadableDatabase().rawQuery(sqlquery,args);
		while(cursor.moveToNext()) {
			houseData data = new houseData();
			data.setMid(cursor.getInt(cursor.getColumnIndex(COLUMN_ZXXHOUSE_ID)));
			data.setMtitle(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_TITLE)));
			data.setMdepartMentName(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_DEPAPTMENT)));
			data.setMaddress(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_ADDRESS)));
			data.setMtotalprice(cursor.getInt(cursor.getColumnIndex(COLUMN_ZXXHOUSE_TOTALORICE)));
			data.setMpmPrice(cursor.getInt(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PMPRICE)));
			data.setMorientation(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_ORIENTATION)));
			data.setMhouseType(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_HOUSETYPE)));
			data.setMdecorate(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_DECORATE)));
			data.setMarea(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_AREA)));
			data.setMbuildType(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_BUILDTYPE)));
			data.setMlouchen(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_LOUCHEN)));
			data.setMbuildAge(cursor.getInt(cursor.getColumnIndex(COLUMN_ZXXHOUSE_BUILDAGE)));
			data.setMtel(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_TEL)));
			data.setMpicPath1(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PICPATH1)));
			data.setMpicPath2(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PICPATH2)));
			data.setMpicPath3(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PICPATH3)));
			data.setMpicPath4(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PICPATH4)));
			data.setMpicPath5(cursor.getString(cursor.getColumnIndex(COLUMN_ZXXHOUSE_PICPATH5)));
			list.add(data);	
		}	
		return list;
		
	}
	
	/**
	 * @brief 查询数据库是不是有重复数据
	 * @param data
	 * @return
	 */
	public boolean selectRepeatData(houseData data)
	{
		String [] agrs = new String[]{data.getMtitle(),data.getMdepartMentName(),data.getMaddress()};
		Cursor cursor = getReadableDatabase().rawQuery("select * from zxxhouse where title = ? and departMent=? and address=?",agrs);
		if(0 < cursor.getCount()) {
			return true;
		}
		return false;
	}
	
	/**
	 * @brief 获取列表剩余项目
	 * @return
	 */
	public int getSortListRest(){
		return getSQLCount("select count(*) from zxxhouse order by id asc",null);
	}
	
	/**
	 * @brief 获取区域剩余项目
	 * @param args 
	 * @return 
	 */
	public int getDepartmentRest(String [] args){
		
		return getSQLCount("select count(*) from zxxhouse where departMent = ? order by id asc",args);
	}
   /**
    * @brief 获取总价格剩余项目(小于100W)
    * @return
    */
	public int getTotalPricebelow100W(){
		return getSQLCount("select count(*) from zxxhouse where totalprice  <= 100 order by totalprice asc",null);
	}
	/**
     * @brief 获取总价格剩余项目(100W_200W之间)
     * @return
     */
	public int getTotalPrice100W_200W(){
		return getSQLCount("select count(*) from zxxhouse where totalprice > 100 and totalprice < 200 order by totalprice asc",null);
	}
	/**
	 * @brief 获取总价格剩余项目(大于200Ｗ)
	 * @return
	 */
	public int getTotalPriceabove200W(){
		return getSQLCount("select count(*) from zxxhouse where totalprice >= 200 order by totalprice asc",null);
	}
	
	/**
	 * 
	 * @brief 获取按房子类型的剩余屋子总数
	 * @param args
	 * @return 
	 */
	public int getHouseTypeRest(String[] args){
		return getSQLCount("select count(*) from zxxhouse where houseType like ? order by id asc",args);
		
	}
	
	/**
	 * @brief 获取按装修类型的房子剩余总数
	 * @param args
	 * @return
	 */
	public int getZXTypeRest(String[] args){
		return getSQLCount("select count(*) from zxxhouse where decorate = ? order by id asc",args);
	}
	
	/**
	  * @brief 获取单价剩余项目(小于1W)
	  * @return
      */
	public int getPmPriceBELOW1W(){
		return getSQLCount("select count(*) from zxxhouse where pmPrice  <= 10000 order by pmPrice asc",null);
	}
	/**
     * @brief 获取单价剩余项目(1W_2W之间)
     * @return
     */
	public int getPmPrice1W_2W(){
		return getSQLCount("select count(*) from zxxhouse where pmPrice > 10000 and pmPrice < 20000 order by pmPrice asc",null);
	}
	/**
	 * @brief 获取单价剩余项目(大于2Ｗ)
	 * @return
	 */
	public int getPmPriceabove2W(){
		return getSQLCount("select count(*) from zxxhouse where pmPrice >= 20000 order by pmPrice asc",null);
	}
	
	/**
	 * @brief 获取数据库按sql语句拼接后查询的总列数
	 * @param sqlquery 数据库拼接sql语句
	 * @param args 数据库拼接sql语句数组
	 * @return 列数
	 */
	
	private int getSQLCount(String sqlquery,String[] args) {
		int  num=0;
		Cursor cursor = getReadableDatabase().rawQuery(sqlquery,args);
		while(cursor.moveToNext()) {
			num += cursor.getInt(0);
		}
		return num;
		
	}
		
	
		
}
